import sqlite3

# 连接到数据库
conn = sqlite3.connect('aichat.db')
cursor = conn.cursor()

print("===== 会话统计分析 =====")

# 获取总消息数
cursor.execute('SELECT COUNT(*) FROM messages')
total_messages = cursor.fetchone()[0]
print(f"总消息数: {total_messages}")

# 获取不同会话数量
cursor.execute('SELECT COUNT(DISTINCT session_id) FROM messages')
distinct_sessions = cursor.fetchone()[0]
print(f"不同会话数: {distinct_sessions}")

# 获取最大的几个会话
print("\n消息数最多的5个会话:")
cursor.execute('''
    SELECT session_id, COUNT(*) as message_count 
    FROM messages 
    GROUP BY session_id 
    ORDER BY message_count DESC 
    LIMIT 5
''')
for row in cursor.fetchall():
    print(f"会话ID: {row[0]}, 消息数: {row[1]}")

# 检查是否有会话ID为空的情况
cursor.execute('SELECT COUNT(*) FROM messages WHERE session_id IS NULL OR session_id = ""')
empty_sessions = cursor.fetchone()[0]
print(f"\n空会话ID的消息数: {empty_sessions}")

# 获取最近的几条消息，查看它们的会话ID
print("\n最近10条消息的会话ID:")
cursor.execute('SELECT session_id, role, content FROM messages ORDER BY created_at DESC LIMIT 10')
for row in cursor.fetchall():
    print(f"会话ID: {row[0]}, 角色: {row[1]}")

conn.close()
